Dynamic selection of optimal grouping sequence at runtime for grouping sets, rollup and cube operations in SQL query processing

ABSTRACT

A method, apparatus, and article of manufacture for optimizing a query in a computer system. During compilation of the query, a GROUP BY clause with one or more GROUPING SETS, ROLLUP or CUBE operations is maintained in its original form until after query rewrite. The GROUP BY clause with the GROUPING SETS, ROLLUP or CUBE operations is then translated into a plurality of levels having one or more grouping sets. After compilation of the query, a grouping sets sequence is dynamically determined for the GROUP BY clause with the GROUPING SETS, ROLLUP or CUBE operations based on intermediate grouping sets, in order to optimize the grouping sets sequence. The execution of the grouping sets sequence is optimized by selecting a smallest grouping set from a previous one of the levels as an input to a grouping set on a next one of the levels. Finally, a UNION ALL operation is performed on the grouping sets.

BACKGROUND OF THE INVENTION

1. Field of the Invention

This invention relates in general to database management systemsperformed by computers, and in particular, to the optimization ofqueries by dynamic selection of optimal grouping sequence at runtime forgrouping sets, rollup and cube operations in SQL query processing.

2. Description of Related Art

Computer systems incorporating Relational DataBase Management System(RDBMS) software using the Structured Query Language (SQL) interface arewell known in the art. SQL has evolved into a standard language forRDBMS software and has been adopted as such by both the AmericanNationals Standard Institute (ANSI) and the International StandardsOrganization (ISO).

To better support business intelligence (BI) and online analyticprocessing (OLAP) applications, a set of grouping operations, inaddition to the standard GROUP BY clause, have recently been includedinto SQL, to allow users to create various groupings of data in a singleSQL query. The set of grouping operations include GROUPING SETS, ROLLUP,CUBE and a scalar GROUPING function.

The GROUP BY clause specifies how the set of rows is to be sub-dividedor grouped. Rows with the same values for the expressions in the groupby clause are grouped together. GROUPING SETS allows the user to specifyseveral groupings of data are to be calculated, including a “subtotal”row. ROLLUP and CUBE are special cases of the generic GROUPING SETSoperation, which can be thought of as “super group” operations becausethey provide many groupings in a single statement. In addition, a newscalar function, GROUPING, is provided to work in conjunction with theseextensions.

What is a needed in the art are techniques that optimize the executionof queries containing a GROUP BY clause with one or more GROUPING SETS,ROLLUP and CUBE operations with or without scalar GROUPING functions.Specifically, there is a need in the art for techniques that avoidunnecessary steps in the execution of such queries. The presentinvention satisfies these needs.

SUMMARY OF THE INVENTION

To overcome the limitations in the prior art described above, and toovercome other limitations that will become apparent upon reading andunderstanding the present specification, the present invention disclosesa method, apparatus, and article of manufacture for optimizing a queryin a computer system, wherein the query is performed by the computersystem to retrieve data from a database stored on the computer system.During compilation of the query, a GROUP BY clause with one or moreGROUPING SETS, ROLLUP or CUBE operations is maintained in its originalform until after query rewrite. The GROUP BY clause with the GROUPINGSETS, ROLLUP or CUBE operations is then translated into a plurality oflevels having one or more grouping sets. After compilation of the query,a grouping sets sequence is dynamically determined for the GROUP BYclause with the GROUPING SETS, ROLLUP or CUBE operations based onintermediate grouping sets, in order to optimize the grouping setssequence. The execution of the grouping sets sequence is optimized byselecting a smallest grouping set from a previous one of the levels asan input to a grouping set on a next one of the levels. Finally, a UNIONALL operation is performed on the grouping sets.

BRIEF DESCRIPTION OF THE DRAWINGS

Referring now to the drawings in which like reference numbers representcorresponding parts throughout:

FIG. 1 illustrates an exemplary computer hardware and softwareenvironment that could be used with an embodiment of the presentinvention;

FIG. 2 is a flowchart illustrating the steps necessary for theinterpretation and execution of SQL statements in an interactiveenvironment according to the present invention;

FIG. 3 is a flowchart illustrating the steps necessary for theinterpretation and execution of SQL statements embedded in source codeaccording to the present invention;

FIG. 4 is a block diagram illustrating how super groups are translatedinto grouping sets in a plurality of levels according to the preferredembodiment of the present invention;

FIG. 5 is a block diagram illustrating a query execution plan for anexemplary query according to the preferred embodiment of the presentinvention; and

FIG. 6 is a flowchart illustrating a method of optimizing a query in acomputer system, the query being performed by the computer system toretrieve data from a database stored on the computer system, accordingto the preferred embodiment of the present invention.

DETAILED DESCRIPTION OF THE PREFERRED EMBODIMENT

In the following description of the preferred embodiment, reference ismade to the accompanying drawings, which form a part hereof, and inwhich is shown by way of illustration a specific embodiment in which theinvention may be practiced. It is to be understood that otherembodiments may be utilized and structural and functional changes may bemade without departing from the scope of the present invention.

OVERVIEW

The present invention dynamically determines a grouping sequence basedon intermediate results sets, in order to choose an optimal groupingorder. During compilation of a query, a GROUP BY clause with one or moreGROUPING SETS, ROLLUP or CUBE operations is maintained in its originalform until after query rewrite. The GROUP BY clause with the GROUPINGSETS, ROLLUP or CUBE operations is then translated into a plurality oflevels having one or more grouping sets. After compilation of the query,a grouping sets sequence is dynamically determined for the GROUP BYclause with the GROUPING SETS, ROLLUP or CUBE operations based onintermediate grouping sets, in order to optimize the grouping setssequence. The execution of the grouping sets sequence is optimized byselecting a smallest grouping set from a previous one of the levels asan input to a grouping set on a next one of the levels. Finally, a UNIONALL operation is performed on the grouping sets. In this manner, runtimeexecution is optimized by choosing a best processing order dynamicallyto minimize execution time.

Hardware and Software Environment

FIG. 1 illustrates an exemplary computer hardware and softwareenvironment that could be used with the present invention. In theexemplary environment, a server system 100 is connected to one or moreclient systems 102, in order to manage one or more databases 104 and 106shared among the client systems 102.

Operators of the client systems 102 use a standard operator interface108 to transmit commands to and from the server system 100 thatrepresent commands for performing various search and retrievalfunctions, termed queries, against the databases. In the presentinvention, these queries conform to the Structured Query Language (SQL)standard, and invoke functions performed by Relational DataBaseManagement System (RDBMS) software. In the preferred embodiment of thepresent invention, the RDBMS software comprises the DB2 product offeredby IBM for the MVS, LINUX, UNIX, WINDOWS or OS/2 operating systems.Those skilled in the art will recognize, however, that the presentinvention has application to any RDBMS software.

As illustrated in FIG. 1, the RDBMS includes three major components: theResource Lock Manager (RLM) 110, the Systems Services module 112, andthe Database Services module 114. The RLM 110 handles locking services,because the RDBMS treats data as a shared resource, thereby allowing anynumber of users to access the same data simultaneously, and thusconcurrency control is required to isolate users and to maintain dataintegrity. The Systems Services module 112 controls the overall RDBMSexecution environment, including managing log data sets 106, gatheringstatistics, handling startup and shutdown, and providing managementsupport.

At the heart of the RDBMS architecture is the Database Services module114. The Database Services module 114 contains several submodules,including a Relational Database System (RDS) 116, Data Manager 118,Buffer Manager 120, and SQL Interpreter 122. These submodules supportthe functions of the SQL language, i.e., definition, access control,retrieval, and update of user and system data.

Generally, each of the components, modules, and submodules of the RDBMScomprise instructions and/or data, and are embodied in or retrievablefrom a computer-readable device, medium, or carrier, e.g., a memory, adata storage device, a remote device coupled to the server computer 100by a data communications device, etc. Moreover, these instructionsand/or data, when read, executed, and/or interpreted by the servercomputer 100, cause the server computer 100 to perform the stepsnecessary to implement and/or use the present invention.

Thus, the present invention may be implemented as a method, apparatus,or article of manufacture using standard programming and/or engineeringtechniques to produce software, firmware, hardware, or any combinationthereof. The term “article of manufacture”, or alternatively, “computerprogram carrier”, as used herein is intended to encompass a computerprogram accessible from any computer-readable device, carrier, or media.

Of course, those skilled in the art will recognize many modificationsmay be made to this configuration without departing from the scope ofthe present invention. Specifically, those skilled in the art willrecognize that any combination of the above components, or any number ofdifferent components, including computer programs, peripherals, andother devices, may be used to implement the present invention, so longas similar functions are performed thereby.

Interactive SQL Execution

FIG. 2 is a flowchart illustrating the steps necessary for theinterpretation and execution of SQL statements in an interactiveenvironment according to the present invention. Block 200 represents theinput of SQL statements into the server system 100. Block 202 representsthe step of compiling or interpreting the SQL statements. Anoptimization function within block 202 may transform or optimize the SQLquery in a manner described in more detail later in this specification.Generally, the SQL statements received as input specify only the desireddata, but not how to retrieve the data. This step considers both theavailable access paths (indexes, sequential reads, etc.) and system heldstatistics on the data to be accessed (the size of the table, the numberof distinct values in a particular column, etc.), to choose what itconsiders to be the most efficient access path for the query. Block 204represents the step of generating a compiled set of runtime structurescalled a query execution plan from the compiled SQL statements. Block206 represents the execution of the query execution plan and Block 208represents the output of the results.

Embedded/Batch SQL Execution

FIG. 3 is a flowchart illustrating the steps necessary for theinterpretation and execution of SQL statements embedded in host languagesource code according to the present invention. Block 300 representsprogram source code containing a host language (such as COBOL or C) andembedded SQL statements. The program source code is then input to apre-compile step 302. There are two outputs from the pre-compile step302: a modified source module 304 and a Database Request Module (DBRM)306. The modified source module 304 contains host language calls to DB2,which the pre-compile step 302 inserts in place of SQL statements. TheDBRM 306 is comprised of the SQL statements from the program source code300. A compile and link-edit step 308 uses the modified source module304 to produce a load module 510, while an optimize and bind step 512uses the DBRM 306 to produce a compiled set of runtime structures forthe query execution plan 514. As indicated above in conjunction withFIG. 2, the SQL statements from the program source code 300 specify onlythe desired data, but not how to retrieve the data. The optimize andbind step 512 may optimize the SQL query in a manner described in moredetail later in this specification. Thereafter, the optimize and bindstep 512 considers both the available access paths (indexes, sequentialreads, etc.) and system held statistics on the data to be accessed (thesize of the table, the number of distinct values in a particular column,etc.), to choose what it considers to be the most efficient access pathfor the query. The load module 510 and query execution plan 514 are thenexecuted together at step 516.

Description of the Optimization Technique

The present invention discloses an improved optimization technique thatis typically performed at steps 204 and 206 of FIG. 2 or steps 312 and316 of FIG. 3. Specifically, the present invention optimizes theexecution of grouping operations, including GROUPING SETS, ROLLUP orCUBE operations, used in conjunction with GROUP BY clauses in SQLqueries executed by the RDBMS.

The GROUPING SETS operation allows the user to specify several groupingsof data are to be calculated, including a “subtotal” row.

The ROLLUP and CUBE operations are special cases of the generic GROUPINGSETS operation. They can be thought of as “super group” operationsbecause they provide many groupings in a single statement. “Sub-total”rows are often used to describe the additional rows provided by ROLLUPand CUBE because the SUM function is commonly used for aggregation.However, other aggregate functions, such as the AVG function, can beused instead. To evaluate other aggregate functions that cannot becomputed directly from the same functions with a different grouping,such as AVG, VAR, and STDDEV, they have to be converted into a formulaof SUM and COUNT. SUM and COUNT can be computed from a lower levelgrouping. Only SUM and COUNT values are kept in intermediate results.The following discussion assumes that this transformation is done forgrouping set computation.

In addition, a scalar GROUPING function is provided to work inconjunction with the GROUPING SETS, ROLLUP or CUBE operations.

The following provides a more detailed description of the GROUP BYclause, the GROUPING. SETS, ROLLUP, and CUBE operations, and the scalarGROUPING function.

GROUP BY Clause

The GROUP BY clause specifies how the set of rows is to be sub-dividedor grouped. Rows with the same values for the expressions in the GROUPBY clause are grouped together.

GROUPING SETS Operation

The GROUPING SETS operation provides the ability to specify multiplegrouping criteria within a single statement. A GROUPING SETS operationcan be a simple grouping, or a “super group”, or a combination of thetwo. Since each GROUPING SETS operation produces a set of rows, theresult of the GROUPING SETS operation is equivalent to a UNION ALL ofthe sets of rows. For each GROUPING SETS operation, the data for inputcolumns (or expressions) is aggregated and returned. Data fornon-aggregated columns that do not appear in the grouping clause are“collapsed,” meaning a NULL value is returned for each such column.Aggregated columns in the select list are considered to be part of eachgrouping.

The following example illustrates the use of the GROUPING SETSoperation. Suppose Joe Stevens owns three car dealerships, one forAudis, one for BMWs, and one for. Porsches. Joe runs a query to find outwhat the inventories were for each dealership at the end of the thirdquarter and at the end of the seventh and eighth months of the quarter.(Assume the base table contains the appropriate data to allow the queryto run.)

Query: SELECT quarter(sales_date) as qtr,     month(sales_date) asmonth, dealership as dealer,     sum(sales) as units_sold FROM car_salesWHERE quarter(sales_date) = 3 GROUP BY GROUPINGSETS((quarter(sales_date)),               (month(sales_date)),              (dealership)) ORDER BY qtr, month, dealer;

The results are: QTR MONTH DEALER UNITS_SOLD 3 — — 730 — 7 — 270 — 8 —460 — — AUDI 320 — — BMW 330 — — PORSCHE 80

The first line is produced by the GROUPING SET(quarter(sales_date)). Thesecond and third lines are produced by the GROUPINGSET(month(sales_date)). The remaining lines are produced by the GROUPINGSET(dealership). Note that some values are shown as NULL (indicated by adash). There is a way to tell when the NULL values are being returned asa result of the GROUPING SET. This is explained in the scalar GROUPINGfunction below.

Scalar GROUPING Function

The scalar GROUPING function can be used in a select list; it works inconjunction with a GROUPING SETS operation. The scalar GROUPING functionenables the user to determine whether a column (or expression) returnedas NULL is because its value really is NULL or, alternatively, becausethe column (or expression) has been “collapsed” in the returned rowbecause it is not part of a GROUPING SETS operation. In the latter case,the grouping value is set to 1; if a column (or expression) value isactually NULL, the grouping value is set to 0.

The following example illustrates the use of GROUPING SETS and scalarGROUPING function together.

Query: SELECT quarter(sales_date) as qtr,     month(sales_date) asmonth, dealership as dealer,     sum(sales) as sold,    GROUPING(quarter(sales_date)) as qtr_grp,    GROUPING(month(sales_date)) as mo_grp,     GROUPING(dealership) asdlr_grp FROM car_sales WHERE quarter(sales_date) = 3 GROUP BY GROUPINGSETS((quarter(sales_date)),               (month(sales_date)),              (dealership) ) ORDER BY qtr, month, dealer;

The results are: QTR_(—) DLR_(—) QTR MONTH DEALER SOLD GRP MO_GRP GRP 3— — 730 0 1 1 — 7 — 270 1 0 1 — 8 — 460 1 0 1 — — AUDI 320 1 1 0 — — BMW330 1 1 0 — — PORSCHE 80 1 1 0

The 1's in the QTR_GRP, MO_GRP and DLR_GRP columns indicate thecorresponding values in the QTR, MONTH, and DEALER columns have beencollapsed; they are not actually NULL. The 0's in the QTR GRP, MO_GRPand DLR_GRP columns indicate the corresponding values in the QTR, MONTH,and DEALER columns are actual values. For example, the 1's in the MO_GRPand DEAL_GRP columns for the first row indicate that the MONTH andDEALER column values have been collapsed, whereas the 0 in the QTR_GRPcolumn indicates the QTR column contains an actual value.

ROLLUP Operation

A ROLLUP operation is an extension to the GROUP BY clause that providessub-total rows in addition to the rows that would normally be produced.A sub-total row is one that contains further aggregate values that arecalculated using the same column function as the one specified in thesub-query used to obtain the groups of rows.

A ROLLUP operation is equivalent to a form of GROUPING SETS operation. AROLLUP operation of “n” grouping expressions results in “n+1” GROUPINGSETS operations. For example, the following:

-   -   GROUP BY ROLLUP (C₁, C₂, . . . , C_(n))

is equivalent to: GROUP BY GROUPING SETS((C₁),              (C₁, C₂),             ...,              (C₁, C₂, ..., C_(n−1)),              (C₁,C₂, ..., C_(n−1), C_(n)),              ( ))

The order of grouping expressions in a ROLLUP operation is significant;they are not commutative. For example, the following:

-   -   GROUP BY ROLLUP (C1, C2, C3)

results in: GROUP BY GROUPING SETS((C1, C2, C3),              (C1, C2),             (C1)              ( ) )whereas, the following:

-   -   GROUP BY ROLLUP (C3, C2, C1)

results in: GROUP BY GROUPING SETS((C3, C2, C1),              (C3, C2),             (C3),              ( ))

The following example illustrates the use of a GROUP BY clause with aROLLUP operation:

Query: SELECT quarter(sales_date) as qtr,     month(sales_date) asmonth, dealership as dealer,     sum(sales) as sold,    GROUPING(quarter(sales_date)) as qtr_grp,    GROUPING(month(sales_date)) as mo_grp,     GROUPING(dealership) asdlr_grp FROM car_sales WHERE quarter(sales_date) = 3 GROUP BY ROLLUP((quarter(sales_date)), (month(sales_date)), (dealership) ) ORDER BY qtr,month, dealer;

The results are: QTR_(—) DLR_(—) QTR MONTH DEALER SOLD GRP MO_GRP GRP 37 AUDI 110 0 0 0 3 7 BMW 120 0 0 0 3 7 PORSCHE 40 0 0 0 3 7 — 270 0 0 13 8 AUDI 210 0 0 0 3 8 BMW 210 0 0 0 3 8 PORSCHE 40 0 0 0 3 8 — 460 0 01 3 — — 730 0 1 1 — — — 730 1 1 1

Note the results are different from the previous query example for theGROUPING function. The major difference is that sub-total rows areproduced. The first sub-total row (line 4) gives the sum of the SOLDvalues for rows where QTR=3 and MONTH=7 with DEALER values collapsed.The second sub-total row (line 8) gives the sum of the SOLD values forrows where QTR=3 and MONTH=8 with DEALER values collapsed. The thirdsub-total row (line 9) gives the sum of the SOLD values for rows whereQTR=3 with MONTH and DEALER values collapsed. The final grand total row(line 10) gives the sum of the SOLD values with QTR, MONTH, and DEALERvalues collapsed. As mentioned before, sub-total or grand total row canbe recognized by the fact that QTR_GRP, MO_GRP, and DLR_GRP contain a 1when a NULL value appears in the corresponding QTR, MONTH, or DEALERcolumn.

CUBE Operation

A CUBE operation is another extension to the GROUP BY clause thatprovides “cross tabulation” rows in addition to all of the rows producedby a ROLLUP operation. Cross tabulation rows are produced by processingadditional combinations of the grouping expressions specified as part ofthe subquery.

A CUBE operation grouping is like a ROLLUP operation grouping in that itcan be expressed as a form of GROUPING SETS operation. For a CUBEoperation, all combinations of the CUBE grouping expressions arecomputed in addition to a grand total row. A CUBE operation of “n”grouping expressions results in 2^(n) GROUPING SETS operation. Forexample, the following:

-   -   GROUP BY CUBE (C1, C2, C3)

produces: GROUP BY GROUPING SETS((C1, C2, C3),              (C1, C2),             (C1, C3),              (C2, C3),              (C1),             (C2),              (C3),              ( ))

The order of grouping expressions in a CUBE operation is notsignificant. Two queries with identical grouping expressions indifferent orders will produce the same rows, but in a different order.The ORDER BY clause can be used to specify the row order.

The following example illustrates the use of GROUP BY clause with a CUBEoperation.

Query: SELECT quarter(sales_date) as qtr,     month(sales_date) asmonth, dealership as dealer,     sum(sales) as units_sold,    GROUPING(quarter(sales_date)) as qtr_grp,    GROUPING(month(sales_date)) as mo_grp,     GROUPING(dealership) asdlr_grp FROM car_sales WHERE quarter(sales_date) = 3 GROUP BY CUBE((quarter(sales_date)), (month(sales_date)), (dealership) ) ORDER BY qtr,month, dealer;

The results are: QTR_(—) DLR_(—) QTR MONTH DEALER SOLD GRP MO_GRP GRPPart 1 of 2: 3 7 AUDI 110 0 0 0 3 7 BMW 120 0 0 0 3 7 PORSCHE 40 0 0 0 37 — 270 0 0 1 3 8 AUDI 210 0 0 0 3 8 BMW 210 0 0 0 3 8 PORSCHE 40 0 0 03 8 — 460 0 0 1 3 — AUDI 320 0 1 0 3 — BMW 330 0 1 0 3 — PORSCHE 80 0 10 3 — — 730 0 1 1 Part 2 of 2: — 7 AUDI 110 1 0 0 — 7 BMW 120 1 0 0 — 7PORSCHE 40 1 0 0 — 7 — 270 1 0 1 — 8 AUDI 210 1 0 0 — 8 BMW 210 1 0 0 —8 PORSCHE 40 1 0 0 — 8 — 460 1 0 1 — — AUDI 320 1 1 0 — — BMW 330 1 1 0— — PORSCHE 80 1 1 0 — — — 730 1 1 1

The result set returned by a CUBE operation is similar to and more thanwhat is returned by a ROLLUP operation. A sub-total row can berecognized by the fact that QTR_GRP, MO_GRP, and DLR_GRP contain a 1when a NULL value appears in the corresponding QTR, MONTH, or DEALERcolumn.

Relationships Between Operations

In some cases, there is a simple relationship among CUBE, ROLLUP, andGROUPING SETS operations. For example, the following:

-   -   GROUP BY CUBE (C1, C2, C3)        produces the same set of rows as:    -   GROUP BY ROLLUP(C1), ROLLUP(C2), ROLLUP(C3)

which produces the same set of rows as: GROUP BY GROUPING SETS((C1, C2,C3),              (C1, C2),              (C1, C3),              (C1),             (C2, C3),              (C2),              (C3),             ( )).

However, this relationship is not true for a slightly more complicatedexample:

-   -   GROUP BY CUBE (c1, (c2, c3), c4)        does not produce the same result as:    -   GROUP BY ROLLUP (c1), ROLLUP(c2, c3), ROLLUP(c4).        The CUBE operation produces 8 grouping sets while the ROLLUP        operation produces 12.

For a single GROUP BY clause, various combinations of GROUPING SETS,ROLLUP, and CUBE operations can be specified. When two or more of thesesets are specified, they are, in effect, “multiplied” together.

For example, the following:

-   -   GROUP BY ROLLUP(a,b), ROLLUP(c,d)

produces results equivalent to: GROUP BY GROUPING SETS((a,b,c,d),             (a,b,c),              (a,c,d),              (a,c),             (a,b),              (c,d),              (a),             (c),              ( ) )

This can be thought of as multiplying (a,b), (a), ( ), the grouping setsfor ROLLUP(a,b), times (c,d), (c), ( ), the grouping sets forROLLUP(c,d).

In another example, the following:

-   -   GROUP BY CUBE(a,b), CUBE(c,d)

produces results equivalent to: GROUP BY GROUPING SETS((a,b,c,d),             (a,b,c),              (a,b,d),              (a,b),             (a,c,d),              (a,c),              (a,d),             (a),              (b,c,d),              (b,c),             (b,d),              (b),              (c,d),             (c),              (d),              ( ) ).

This can be thought of as multiplying (a,b), (a), (b), ( ), the groupingsets for CUBE(a,b), times (c,d), (c), (d), ( ), the grouping sets forCUBE(c,d).

In yet another example, the following:

-   -   GROUP BY ROLLUP(a,b,c), CUBE(d,e)

produces results equivalent to: GROUP BY GROUPING SETS((a,b,c,d,e),             (a,b,c,d),              (a,b,c,e),              (a,b,c),             (a,b,d,e),              (a,b,d),              (a,b,e),             (a,b),              (a,d,e),              (a,d),             (a,e),              (a),              (d,e),             (d),              (e),              ( )).

This can be thought of as multiplying (a,b,c), (a,b), (a), ( ), thegrouping sets for ROLLUP(a,b,c), times (d,e), (d), (e), ( ), thegrouping sets for CUBE(c,d).

The result of a grouping set that is comprised of a subset of groupingexpressions of another grouping set can be computed using GROUP BY fromthe result of the other grouping set. For example, the grouping set (a,d) can be computed or derived from the grouping set (a, b, d), or thegrouping set (a, b, c, d), or the grouping set (a, b, c, d, e), byapplying GROUP BY a, d.

Optimization of GROUP BY Clauses, GROUPING SETS, ROLLUP and CUBEOperations, and Scalar GROUPING Functions

The following describes the optimization of GROUP BY clauses, GROUPINGSETS, ROLLUP and CUBE operations, and scalar GROUPING functionsaccording to a preferred embodiment of the present invention.

For example, given a GROUP BY clause with ROLLUP(l,f,g), CUBE(m,s,c)operations, the following steps are performed by the RDBMS:

1. Translating the GROUP BY clause with ROLLUP and CUBE operations intogrouping sets. For the above GROUP BY clause, there are 32 grouping setsin 7 levels, where each level contains grouping sets with the samenumber of grouping expressions: level 6 (l,f,g,m,s,c) level 5(l,f,g,m,s), (l,f,g,m,c), (l,f,g,s,c), (l,f,m,s,c), ... level 4(l,f,m,s), (l,f,m,c), (l,f,s,c), (l,m,s,c), ... ... level 1 (l), (m),(s), (c) level 0 ( )

2. Building result of each next level grouping set by selecting asmallest grouping set from a previous level as its input.

3. Performing a UNION ALL operation on the result of the grouping sets.

The present invention has a number of advantages:

(1) The present invention performs equally well or better than prior artapproaches, since the RDBMS chooses a smallest grouping set from aprevious level as input to a next level grouping set, and is notaffected by the ordering of groupings in the query. As the data changes,the optimal sequence will change, but will always be dynamically chosen.

(2) The present invention translates super groups into execution formduring the last phase of compilation, and no query rewrite is required.This means there is little impact to the optimizer during the queryrewrite and access path selection phases. Furthermore, bind time storagerequirements are minimized and a shorter bind time is expected,especially for complex groupings.

(3) The present invention maintains the query in its original form(instead of rewriting it during the early optimizer phases into complextable expressions). This simplifies the process of recognizing andexploiting summary tables, assuming they can be used for the query.

(4) Implementation of the present invention is straightforward with nospecial case logic required.

(5) Workfiles created during processing do not need to carry values (0or 1) for each grouping set column to indicate whether it is a subtotalor total (i.e. the GROUPING function value), since grouping sets are notmixed (applies to alternative 1 only below).

The potential disadvantage is that more workfiles are generated, but theworkfiles are smaller and the total size in rows is the same.

Implementation of the Optimization Techniques

The improved optimization techniques of the present invention aredescribed using a graph that shows the derivation relationships betweengrouping sets. Each box in the graph represents a grouping set, whilearcs between boxes are used to represent derivation relationships. Asseen before, the grouping sets are arranged in levels. Only derivationsfrom one level apart are represented, since the smallest input is alwaysfrom one level apart and any derivation that skips levels will not be asefficient.

Following is an example of the implementation of the optimizationtechnique of the preferred embodiment of the present invention. Assume aquery is comprised of the following:

-   -   SELECT . . . FROM . . . WHERE . . . GROUP BY CUBE (a, b, c),        ROLLUP (d, e, f);

After compilation of the query, the super groups comprising the CUBE andROLLUP operations are translated into the 32 grouping sets, which areshown as boxes in FIG. 4. Note that FIG. 4 shows only a subset ofderivation relationships, represented by arrows, between boxes for thesake of clarity. A complete representation of the relationships in FIG.4 would include arrows from every next level box to one or more previouslevel boxes where the corresponding previous level grouping set can beused to derive the corresponding next level grouping set.

The largest or base grouping set at the top of FIG. 0.4 is obtained fromthe tables specified in the FROM clause of the query. The grouping setsat the second level are obtained by performing GROUP BYs on the basegrouping set; the grouping sets in the third level are obtained byperforming GROUP BYs on the grouping sets at the second level; etc.

Note that in some cases, a grouping set can be obtained from more thanone predecessor. For example, (b, d, e, f) at the second level of FIG. 4can be obtained from either (a, b, d, e, f) or (b, c, d, e, f) at thefirst level of FIG. 4. At runtime, the RDBMS dynamically determineswhich grouping set to use based upon the sizes of these grouping sets.Thus, to obtain (b, d, e, f), the RDBMS chooses the smaller of (a, b, d,e, f) and (b, c, d, e, f). This process is repeated at each level inFIG. 4.

After the query is processed by a compiler of the RDBMS, a parse treeand other data structures resulting therefrom are passed to an executionstructure generation function of the RDBMS. The execution structuregeneration function of the RDBMS translates the super groups found inthe parse tree into equivalent grouping sets, for example, as shown inFIG. 4. Then, the RDBMS builds a super group structure that is similarto the hierarchy in FIG. 4 for use in optimizing the execution of thesuper groups.

The following describes more detailed processing steps for the aboveexample.

Compilation/Bind Time

A query execution plan (QEP) 500 for the example query is shown in FIG.5, wherein the QEP 500 includes two runtime structures (RS) 502, 504 forperforming SELECT statements, a GROUP BY (GB) operation 506, a supergroup (SG) operation 508, and a Super Group Block 510. The Super GroupBlock 510 includes a Header 512 having one or more pointers to one ormore workfiles 514 and an array of pointers 516 to linked lists of SuperGroup Elements 518 representing grouping sets for different levels.

The execution structure generation function of the RDBMS has the primaryresponsibility for translating the super groups into execution form. Thefollowing bullets summarize the required steps.

(1) When a GB operation 506 with one or more SG operations 508 isencountered, the RDBMS will construct the Super Group Block 510 in theQEP. The SG operation 508 in such a situation includes an implicit UNIONALL operation.

(2) The RDBMS translates the GROUPING SETS, CUBE and/or ROLLUPoperations associated with the GB operation 506 into equivalent groupingsets.

(3) If a combination of grouping sets or super groups has beenspecified, the RDBMS will “multiply” the combination together todetermine an equivalent result in terms of grouping sets. In theexample, the result of multiplying the CUBE and ROLLUP is 32 groupingsets. The grouping set with the maximum number of grouping columns isthe base grouping set. If no GROUPING SETS, CUBE or ROLLUP are involved,the base grouping set includes all the columns in the grouping sets.

(4) The RDBMS constructs the Super Group Block 510 and attaches it tothe SG operation 508 in the QEP:

(a) The RDBMS allocates and initializes the Super Group Block 510. TheHeader 512 includes a pointer to workfiles 514. An array of pointers 516resides in the Super Group Block 510, wherein each pointer 516 points toa linked list of Super Group Elements 518 representing grouping sets fora particular level. The number of pointers 516 in the array correspondsto the number of columns in a base grouping set. In the example, therewould be 6 entries in the array (although only 4 entries are shown inFIG. 5), because there are 6 grouping columns in the base grouping set(a, b, c, d, e, f), as shown in FIG. 4.

Each array pointer 516 is an anchor for a series of Super Group Elements518 representing grouping sets with the same number of grouping columns.In the example, the first array pointer 516 is an anchor for Super GroupElements 518 representing grouping sets with 6 grouping columns; thesecond array pointer 516 is an anchor for Super Group Elements 518representing grouping sets with 5 grouping columns; etc.

At least one Super Group Element 518 is allocated for each of thegrouping sets, wherein the Super Group Element 518 contains theinformation for a given grouping set and a pointer to a next Super GroupElement 518 in the series. Consequently, the Super Group Elements 518comprises elements of a linked list. For example, all of the Super GroupElements 518 representing grouping sets with exactly 4 grouping columnsare linked together in a linked list.

(b) The RDBMS assigns each grouping set to its corresponding link listaccording to the number of grouping columns in the grouping set.

(c) Each Super Group Element 518 includes one or more pointers to one ormore Super Group Elements 518 on a previous (higher) level, to indicatethat the Super Group Elements 518 on a previous (higher) level can beused as an input source to generate the grouping set of the Super GroupElement 518. In the example, grouping set (a, d, e, f) can be generatedfrom either grouping set (a, b, d, e, f) or grouping set (a, c, d, e,f). Therefore, the Super Group Element 518 for grouping set (a, d, e, f)has two pointers pointing to the Super Group Elements 518 for groupingsets (a, b, d, e, f) and (a, c, d, e, f).

Alternatively, to reduce the pointers used, an implementation can use abit map to encode each grouping set. Each bit in the bit map correspondsto a column in the base grouping set. For a grouping set, if a columnpresents in the grouping set, then its corresponding bit in the bit mapis 1, otherwise it's 0. An explicit pointer for the derivationrelationship can be replaced by checking that a bit map is a subset ofanother bit map at the previous level. An iteration through the SuperGroup Elements at the previous level can find all the input candidatesfor a Super Group Element.

(d) The information to identify the missing columns in the grouping setis saved in the Super Group Element 518. It can be 0's and 1's (1 forthe missing column) or bit strings. This information can also be used tosupport the scalar GROUPING function at runtime.

(e) For each grouping set, the input to sort for the GROUP BY andaggregations contains the same number of elements. For a column of thebase grouping set that is not a column of the current grouping set, theinput points to a NULL value, so that the column will contain a NULLvalue in the workfile 514 of the GROUP BY sort. For example, column “c”will point to a NULL in the grouping set (a, b, d, e, f). Moreover, thenumber of sort key fields might be different for each sort, since thesort keys are the grouping expressions for the current grouping set.

(f) Also, if there is an aggregate function specified for a column inthe SELECT list of the query, there will also be an entry to representthe aggregate function's result. The GROUPING function result entry willpoint to an integer value of 0, if the argument of GROUPING functionappears in the grouping column of the current grouping set. The GROUPINGfunction result entry will point to an integer value of 1, if theargument of GROUPING function does not appear in the grouping column ofthe current grouping set.

Execution Time

(1) During execution, when a SG operation 508 is encountered, the RDBMStraverses the Super Group Structure 510, and performs a GROUP BY sortfor each Super Group Element 518 in the structure 510 on alevel-by-level (top-to-bottom or previous-to-next) basis.

(2) The input for the Super Group Element 518 representing the basegrouping set, i.e., grouping set (a, b, c, d, e, f) in the example, isobtained from RS 504 by the SG operation 508.

(3) The inputs for the Super Group Elements 518 representing the secondlevel grouping sets, i.e., grouping sets (a, b, d, e, t), (a, c, d, e,f), (b, c, d, e, f) and (a, b, c, d, e,) in the example, are obtainedfrom the base grouping set.

(4) For subsequent levels, the inputs for the Super Group Elements 518representing next level grouping sets are obtained from previous levelgrouping sets. Each grouping set in a level may have more than onepossible input and may have a different input than other grouping setsin the level. According to the present invention, the RDBMS determinesthe cardinalities of the input for each grouping set to select the inputwith lowest cardinality. Note that Super Group Element 518 may generateits own workfile as an output and the RDBMS may determine thecardinalities of these workfiles to determine which are to be selectedas the inputs for the next level grouping sets.

(5) When the RDBMS finishes processing the SG operation 508, all resultsof the grouping sets in the Super Group Structure 510 should begenerated.

(6) In the example, the RS operation 502 traverses the Super GroupElements 518 in the Super Group Block 510 and select the workfiles 514accordingly. This is equivalent to an implicit UNION ALL operation.

(7) If system resources (e.g., the number of workfiles 514 generated)are an issue with this architecture, there are two alternatives toreduce the number of workfiles 514, as described below.

Alternative 1

(1) To minimize the workfiles 514 generated at a given point of time,the RDBMS can generate the base grouping set, i.e., grouping set (a, b,c, d, e, f) in the example, only when processing the SG operation 508.

(2) The other grouping sets can be generated on-demand at the time theRDBMS traverses the Super Group Block 510, i.e., only when executing theRS operation 502. This means that the RDBMS invokes sort to perform theGB operation 506 sort for a grouping set when it is required to fetchthe next row from a grouping set that has not yet been generated.

(3) In this manner, the RDBMS can free up any workfile 514 which hasbeen fetched and processed already and is no longer needed. For example,if the RDBMS is executing level “n” in the Super Group Block 510, theworkfiles 514 in level “n-2” can be released, since the grouping sets oflevel “n” can only come from level “n-1.”

Alternative 2

(1) Another approach is to collapse all the results from the groupingsets into a single workfile 514, i.e., a Super Group Workfile 514.

(2) The GROUP BY sort result is appended to the Super Group Workfile 514when the GROUP BY sort is performed for each Super Group Element 518.

(3) The beginning and ending positions or record ids (RIDs) of thecurrent grouping set are kept in the Super Group Element 518 for eachgrouping set appended to the Super Group Workfile.

(4) When generating the grouping sets on a next level, a sort operationhas to fetch the data from the Super Group Workfile 514 according to theRIDs in the Super Group Element 518 chosen.

(5) When the RDBMS needs to fetch the data from a grouping set, it canalso use the RIDs kept in the Super Group Element 518 to reposition andfetch the data from the Super Group Workfile 514.

Parallelism Support

Parallelism support for super groups is implemented in a manner similarto a GROUP BY sort, because the SG operation is, in essence, a pluralityof GROUP BY sorts. The SG operation can be performed as either aconsumer or producer task.

As a consumer task, the SG operation is performed at “consumer time”after the data is fetched. Alternatively, the SG operation may be pusheddown to producer tasks, wherein each producer task performs its own SGoperation against its own data, each producer task has its own SuperGroup Block, and the corresponding workfiles of each Super Group Blockare merged together before a final GROUP BY and aggregation isperformed.

Logic of the Preferred Embodiment

FIG. 6 is a flowchart illustrating a method of optimizing a query in acomputer system, the query being performed by the computer system toretrieve data from a database stored on the computer system, accordingto the preferred embodiment of the present invention.

Block 600 represents the RDBMS, during compilation of the query,maintaining a GROUP BY clause with one or more GROUPING SETS, ROLLUP orCUBE operations in its original form until after query rewrite.

Block 602 represents the RDBMS, at a later stage of query compilation,translating the GROUP BY clause with one or more GROUPING SETS, ROLLUPor CUBE operations into equivalent grouping sets arranged into aplurality of levels having one or more grouping sets, and generatingquery execution plan 500 with a super group block 510.

Block 604 represents the RDBMS, at the query execution time, dynamicallydetermining a grouping sets sequence for the GROUP BY clause with theGROUPING SETS, ROLLUP or CUBE operations based on intermediate groupingsets, in order to optimize the grouping sets sequence. This stepincludes: (1) performing GROUP BY for the base grouping set and thenoptimizing execution of the grouping sets sequence by selecting asmallest grouping set from a previous one of the levels as an input to agrouping set on a next one of the levels, and (2) performing a UNION ALLoperation on the grouping sets.

CONCLUSION

This concludes the description of the preferred embodiment of theinvention. The following describes some alternative embodiments foraccomplishing the present invention. For example, any type of computer,such as a mainframe, minicomputer, or personal computer, could be usedwith the present invention. In addition, any software program performingdatabase queries with grouping and/or aggregation could benefit from thepresent invention.

In summary, the present invention discloses a method, apparatus, andarticle of manufacture for optimizing a query in a computer system,wherein the query is performed by the computer system to retrieve datafrom a database stored on the computer system. During compilation of thequery, a GROUP BY clause with one or more GROUPING SETS, ROLLUP or CUBEoperations is maintained in its original form until after query rewrite.The GROUP BY clause with the GROUPING SETS, ROLLUP or CUBE operations isthen translated into a plurality of levels having one or more groupingsets. After compilation of the query, a grouping sets sequence isdynamically determined for the GROUP BY clause with the GROUPING SETS,ROLLUP or CUBE operations based on intermediate grouping sets, in orderto optimize the grouping sets sequence. The execution of the groupingsets sequence is optimized by selecting a smallest grouping set from aprevious one of the levels as an input to a grouping set on a next oneof the levels. Finally, a UNION ALL operation is performed on thegrouping sets.

The foregoing description of the preferred embodiment of the inventionhas been presented for the purposes of illustration and description. Itis not intended to be exhaustive or to limit the invention to theprecise form disclosed. Many modifications and variations are possiblein light of the above teaching.

1. A method of optimizing a query in a computer system, the query beingperformed by the computer system to retrieve data from a database storedon the computer system, the method comprising: (a) during compilation ofthe query, maintaining a GROUP BY clause with one or more GROUPING SETS,ROLLUP or CUBE operations in its original form until after queryrewrite; and (b) translating the GROUP BY clause with the GROUPING SETS,ROLLUP or CUBE operations into a plurality of levels having one or moregrouping sets.
 2. The method of claim 1, further comprising: (1) aftercompilation of the query, dynamically determining a grouping setssequence for the GROUP BY clause with the GROUPING SETS, ROLLUP or CUBEoperations based on intermediate grouping sets, in order to optimize thegrouping sets sequence. (2) optimizing execution of the grouping setssequence by selecting a smallest grouping set from a previous one of thelevels as an input to a grouping set on a next one of the levels.
 3. Themethod of claim 2, further comprising performing a UNION ALL operationon the grouping sets.
 4. A computer-implemented apparatus for optimizinga query, the query being performed to retrieve data from a database, theapparatus comprising: (a) a computer system; (b) logic, performed by thecomputer system, for (1) during compilation of the query, maintaining aGROUP BY clause with one or more GROUPING SETS, ROLLUP or CUBEoperations in its original form until after query rewrite; and (2)translating the GROUP BY clause with the GROUPING SETS, ROLLUP or CUBEoperations into a plurality of levels having one or more grouping sets.5. The apparatus of claim 4, further comprising logic for: (1) aftercompilation of the query, dynamically determining a grouping setssequence for the GROUP BY clause with the GROUPING SETS, ROLLUP or CUBEoperations based on intermediate grouping sets, in order to optimize thegrouping sets sequence. (2) optimizing execution of the grouping setssequence by selecting a smallest grouping set from a previous one of thelevels as an input to a grouping set on a next one of the levels.
 6. Theapparatus of claim 5, further comprising logic for performing a UNIONALL operation on the grouping sets.
 7. An article of manufactureembodying logic for performing a method for optimizing a query, thequery being performed by a computer system to retrieve data from adatabase stored in a data storage device coupled to the computer system,the method comprising: (a) during compilation of the query, maintaininga GROUP BY clause with one or more GROUPING SETS, ROLLUP or CUBEoperations in its original form until after query rewrite; and (b)translating the GROUP BY clause with the GROUPING SETS, ROLLUP or CUBEoperations into a plurality of levels having one or more grouping sets.8. The article of manufacture of claim 7, further comprising: (1) aftercompilation of the query, dynamically determining a grouping setssequence for the GROUP BY clause with the GROUPING SETS, ROLLUP or CUBEoperations based on intermediate grouping sets, in order to optimize thegrouping sets sequence. (2) optimizing execution of the grouping setssequence by selecting a smallest grouping set from a previous one of thelevels as an input to a grouping set on a next one of the levels.
 9. Thearticle of manufacture of claim 8, further comprising performing a UNIONALL operation on the grouping sets.